package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.*;

public class DBSalesOrder implements IFDBSalesOrder {
	
	private Connection con = DBConnection.getInstance().getDBcon();
	private DBCustomer dbC;
	private DBPartOrder dbPO;
	private DBInvoice dbI;	
	public DBSalesOrder()
	{
		con = DBConnection.getInstance().getDBcon();
		
		dbC = new DBCustomer();
		dbPO = new DBPartOrder();
		dbI = new DBInvoice();
	}

	@Override
	public ArrayList<SalesOrder> getAllSalesOrder() 
	{
		return miscWhere("");
	}
	
	public ArrayList<SalesOrder> getAllSalesOrder(String filter) {
		String wClause = " customerPhone LIKE '%" + filter + "%'";
		return miscWhere(wClause);
	}

	@Override
	public SalesOrder searchSalesOrder(int soId) 
	{
		String wClause = " orderId = '" + soId + "'";
		return singleWhere(wClause);
	}

	@Override
	public int insertSalesOrder(SalesOrder so) throws Exception 
	{
		int nextID = GetMax.getMaxId("Select max(orderId) from SalesOrder");
        nextID = nextID + 1;
        System.out.println("next id = " +  nextID);
		int rc = -1;
		String query="INSERT INTO SalesOrder(orderId, date, totalPrice, deliveryStatus, deliveryDate, freightPrice, CODCharge, discount, customerPhone, invoiceNo)  VALUES('"+
				nextID  + "','"  +
				so.getDate()  + "','"  +
				so.getTotalPrice() + "','" +
				so.getDeliveryStatus() + "','" +
				so.getDeliveryDate() + "','" +
				so.getFreightPrice() + "','" +
				so.isCODCharge() + "','" +
				so.getDiscount() + "','" +
				so.getC().getPhoneNo() + "','" +
				so.getI().getInvoiceNo() + "')";

		System.out.println("insert : " + query);
		try{ // insert new supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			
			insertPartOrders(so.getPo(), nextID);
			
		}//end try
		catch(SQLException ex){
			System.out.println("SalesOrder not created");
			throw new Exception ("SalesOrder is not inserted correct");
		}
		return(rc);
	}

	private void insertPartOrders(ArrayList<PartOrder> poList, int id) throws Exception {
		for(PartOrder po : poList)
		{
			po.getSo().setOrderId(id);
			dbPO.insertPartOrder(po);
		}
	}

	@Override
	public int updateSalesOrder(SalesOrder so) 
	{
		SalesOrder soObj  = so;
		int rc=-1;
		//orderId, date, totalPrice, deliveryStatus, deliveryDate, freightPrice, 
				//CODCharge, discount, customerPhone, invoiceNo
		String query="UPDATE SalesOrder SET "+
				"date ='"+ soObj.getDate() + "', " +
				"totalPrice ='"+ soObj.getTotalPrice() + "', " +
				"deliveryStatus ='"+ soObj.getDeliveryStatus() + "', " +
				"deliveryDate ='"+ soObj.getDeliveryDate() + "', " +
				"freightPrice ='"+ soObj.getFreightPrice() + "', " +
				"CODCharge ='"+ soObj.isCODCharge() + "', " +
				"discount ='"+ soObj.getDiscount() + "', " +
				"customerPhone ='"+ soObj.getC().getPhoneNo() + "' " +
				"invoiceNo ='"+ soObj.getI().getInvoiceNo() + "' " +
				" WHERE orderId = '"+ soObj.getOrderId() + "'";
		System.out.println("Update query:" + query);
		try{ // update employee
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
			updatePartOrders(soObj.getPo(), soObj.getOrderId());
		}//end try
		catch(Exception ex){
			System.out.println("Update exception in SalesOrder db: "+ex);
		}
		return(rc);
	}

	private void updatePartOrders(ArrayList<PartOrder> poList, int orderId) throws Exception {
		for(PartOrder po : dbPO.getAllPartOrders(orderId))
		{
			dbPO.deletePartOrder(po);
		}
		for(PartOrder po : poList)
		{
			dbPO.insertPartOrder(po);
		}
	}

	@Override
	public int deleteSalesOrder(SalesOrder so) 
	{
		int rc=-1;

		String query="DELETE FROM SalesOrder WHERE orderId = '" +
				so.getOrderId() + "'";
		System.out.println(query);
		try{ // delete from supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			deletePartOrder(so.getOrderId());
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in SalesOrder db: "+ex);
		}
		return(rc);
	}
	
	private void deletePartOrder(int orderId) {
		for(PartOrder po : dbPO.getAllPartOrders(orderId))
		{
			dbPO.deletePartOrder(po);
		}
		
	}

	private ArrayList<SalesOrder> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();	

		String query =  buildQuery(wClause);

		try{ // read the supplier from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while( results.next() ){
				SalesOrder soObj = new SalesOrder();
				soObj = buildSalesOrder(results);	
				list.add(soObj);	
			}//end while
			stmt.close();  

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	private SalesOrder singleWhere(String wClause)
	{
		ResultSet results;
		SalesOrder soObj = new SalesOrder();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the product from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				soObj = buildSalesOrder(results);
				stmt.close();
			}
			else{ //no product was found
				soObj = null;
			}
		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return soObj;
	}
	
	private String buildQuery(String wClause)
	{
		String query="SELECT orderId, date, totalPrice, deliveryStatus, deliveryDate, freightPrice, CODCharge, discount, customerPhone, invoiceNo FROM SalesOrder";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	
	private SalesOrder buildSalesOrder(ResultSet results)
	{   SalesOrder soObj = new SalesOrder();
	try{ // the columns from the table supplier are used
		soObj.setOrderId(results.getInt("orderId"));
		soObj.setDate(results.getString("date"));
		soObj.setTotalPrice(results.getDouble("totalPrice"));
		soObj.setDeliveryStatus(results.getString("deliveyStatus"));		
		soObj.setDeliveryDate(results.getString("deliveryDate"));	
		soObj.setFreightPrice(results.getInt("freightPrice"));	
		soObj.setCODCharge(results.getBoolean("CODCharge"));	
		soObj.setC(dbC.getCustomerByPhoneNo(results.getInt("CustomerPhone"),false));
		soObj.setI(dbI.getInvoiceNo(results.getInt("invoiceNo")));
		//soObj.setPo(dbPO.getAllPartOrders(results.getInt("orderId")));
	}
	catch(Exception e)
	{
		System.out.println("error in building the PartOrder object");
	}
	return soObj;
	}
	
}
